package com.shopping.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.shopping.user.User;
import com.shopping.util.DB;
import com.shopping.util.TimeUtil;

public class UserDAO {

		public static void save(User u) {
			Connection conn = null;
			PreparedStatement prepStat = null;
			try {
				conn = DB.getConn();
				conn.setAutoCommit(false);
				String sql = "insert into ruser values (null,?,?,?,?,?)";
				prepStat = DB.getPrepStat(conn,sql );
				
				prepStat.setString(1,u.getUsername());
				prepStat.setString(2,u.getPassword());
				prepStat.setString(3,u.getPhone());
				prepStat.setString(4,u.getAddr());
				prepStat.setString(5, TimeUtil.getStringFromTime(u.getPdate(), "yyyy-MM-dd HH:mm:ss"));
				
				prepStat.executeUpdate();
				conn.commit();
				conn.setAutoCommit(true);
				
			}catch(SQLException e) {
				e.printStackTrace();
			} finally {
				DB.closePrepStat(prepStat);
				DB.closeConn(conn);
			} 
		}

		 public static List<User> getUsers() {
			 List<User> list = new ArrayList<User> ();
			 Connection conn = null;
			 ResultSet rs= null;
			 
			 try {
				 conn = DB.getConn();
				 String sql = "select * from ruser";
				 rs = DB.getRs(conn, sql);
				 while(rs.next()) {
					 User u = new User();
					 u.setUsername(rs.getString("username"));
					 u.setPhone(rs.getString("phone"));
					 u.setAddr(rs.getString("addr"));
					 u.setPdate(rs.getTimestamp("rdate"));
					 u.setId(rs.getInt("id"));
					 list.add(u);
				 }
			 } catch(SQLException e) {
				 e.printStackTrace();
			 } finally {
				 DB.closeRs(rs);
				 DB.closeConn(conn);
			 }
			 
			return list;
		}

		/*public static User validate(String username, String password) throws UsernameNotFoundException,PasswordNotCorrectionException {
			Connection conn = null;
			ResultSet rs = null;
			User u = null;
			String sql = "select * from ruser where username = '" + username + "'";
			System.out.println(sql);
			try {
				conn = DB.getConn();
				rs = DB.getRs(conn, sql);
				if(!rs.next()) {
					throw new UsernameNotFoundException();
				} else if(!rs.getString("password").equals(password)) {
					throw new PasswordNotCorrectionException();
				} else {
					u = new User();
					u.setUsername(rs.getString("username"));
					u.setPassword(rs.getString("password"));
					u.setPhone(rs.getString("phone"));
					u.setAddr(rs.getString("addr"));
				}
			}catch(SQLException e) {
				e.printStackTrace();
			} finally {
				DB.closeRs(rs);;
				DB.closeConn(conn);
			}
			return u;
		}*/
		
		/**
		 * 根据用户名查询User
		 * 
		 * @date 2018-06-04
		 */
		public static User findByUsername(String username) {
			Connection conn = null;
			ResultSet rs = null;
			User u = null;
			String sql = "select * from ruser where username = '" + username + "'";
			System.out.println(sql);
			try {
				conn = DB.getConn();
				rs = DB.getRs(conn, sql);
				if(rs.next()) {
					u = new User();
					u.setUsername(rs.getString("username"));
					u.setPassword(rs.getString("password"));
					u.setPhone(rs.getString("phone"));
					u.setAddr(rs.getString("addr"));
					u.setId(rs.getInt("id"));
				} 
			}catch(SQLException e) {
				e.printStackTrace();
			} finally {
				DB.closeRs(rs);
				DB.closeConn(conn);
			}
			return u;
		}

		public static void userChanged(User u,String username) {
			Connection conn = null;
			PreparedStatement prepStat = null;
			
			
			
			try {
				User u1 = findByUsername(username);
				String sql = "update ruser set username=?,password=?,phone=? where id = " + u1.getId();
				System.out.println(u1.getId());
				conn = DB.getConn();
				conn.setAutoCommit(false);
				prepStat = DB.getPrepStat(conn, sql);
				
				prepStat.setString(1, u.getUsername());
				prepStat.setString(2, u.getPassword());
				prepStat.setString(3, u.getPhone());
				prepStat.executeUpdate();
				
				conn.commit();
				conn.setAutoCommit(true);
				
			}catch(SQLException e) {
				e.printStackTrace();
			} finally {
				DB.closePrepStat(prepStat);;
				DB.closeConn(conn);
			}
			
		}

		public static void delete(int id) {
			Connection conn = null;
			Statement stat = null;
			String sql = "delete from ruser where id = " + id ;
			System.out.println(sql);
			try {
				conn = DB.getConn();
				stat = DB.getStat(conn);
				stat.executeUpdate(sql);
				
			}catch(SQLException e) {
				e.printStackTrace();
			} finally {
				DB.closeStat(stat);
				DB.closeConn(conn);
			}
			
		}
			
		
		
}
